//
//  CZStatusDAL.swift
//  WeiBo
//
//  Created by Apple on 16/9/12.
//  Copyright © 2016年 KY. All rights reserved.
//

import UIKit

// 删除缓存数据的最大时间
let maxTime: TimeInterval = -7 * 24 * 60 * 60

//  数据库访问层, 用于缓存本地数据， 封装网络请求
class CZStatusDAL: NSObject {

    //  加载数据的业务逻辑处理
    class func loadData(maxId: Int64, sinceId: Int64, callBack: @escaping ([[String: Any]])->()) {
        
        //  1. 检查本地是否有缓存数据
        let result = queryCacheData(maxid: maxId, sinceid: sinceId)
        if result.count > 0 {
            //  2. 有缓存数据返回缓存数据
            callBack(result)
            return
        }
        
        //  3. 没有缓存数据从网络加载新浪微博数据
        CZNetworkTools.sharedTools.requestStatus(accesstoken: CZUserAccountViewModel.sharedUserAccount.accessToken!, maxId: maxId, sinceId: sinceId) { (response, error) in
            
            if error != nil {
                print("网络请求异常:\(error)")
                callBack(result)
                return
            }
            
            //  代码执行到此，请求成功，完成字典转模型
            guard let dic = response as? [String: Any] else {
                print("字典格式不正确")
                callBack(result)
                return
            }
            
            guard let dicArray = dic["statuses"] as? [[String: Any]] else {
                print("字典数据为nil")
                callBack(result)
                return
            }
            
            //  4. 网络数据回来后存储到本地
            CZStatusDAL.cacheData(statusDicArray: dicArray)
            //  5. 把网络请求的数据返回
            callBack(dicArray)
        }
        
        
        
        
        
        
    
    }
    
    //  查询本地缓存数据
    class func queryCacheData(maxid: Int64, sinceid: Int64) -> [[String: Any]] {
        //  SELECT * FROM statuses where statusid < 4018841543538749 and userid =  1800530611 order by statusid desc limit 20
        //  拼接sql
        var sql = "SELECT * FROM statuses\n"
        //  上拉加载还是下拉刷新的判断
        if maxid > 0 {
            sql += "where statusid < \(maxid)\n"
        } else {
            sql += "where statusid > \(sinceid)\n"
        }
        let userid = CZUserAccountViewModel.sharedUserAccount.userAccount?.uid ?? 0
        //  拼接用户id
        sql += "and userid = \(userid)\n"
        //  根据微博id降序
        sql += "order by statusid desc\n"
        //  最多请求20条数据
        sql += "limit 20\n"
        
        //  执行sql
        let result = SqliteManager.sharedManager.querySqlToDic(sql: sql)
        
        //  定义微博数据集合
        var tempArray = [[String: Any]]()
        //  遍历查询的结果
        for dic in result {
            
            //  微博字典
            let statusDic = try! JSONSerialization.jsonObject(with: dic["status"]! as! Data, options: []) as! [String: Any]
            tempArray.append(statusDic)
            
        }
        
        return tempArray
       
        
        
    }
    
    
    
    //  缓存网络请求的数据
    class func cacheData(statusDicArray: [[String: Any]]) -> Void {
        
        //  准备sql
        //  有相同数据给其替换
        let sql = "insert or replace into statuses(statusid, status, userid) values(?, ?, ?)"
        //  用户id
        let userid = CZUserAccountViewModel.sharedUserAccount.userAccount?.uid
        //  执行sql
        SqliteManager.sharedManager.queue.inTransaction { (db, rollBack) in
            
            //  遍历数组字典依次插入数据
            for dic in statusDicArray {
                //  微博id
                let statusid = dic["id"]!
                print(statusid)
                //  微博内容，让其转成二进制数据，存入
                let statusData = try! JSONSerialization.data(withJSONObject: dic, options: [])
                
                
                //  插入需要缓存的数据
                let result = db!.executeUpdate(sql, withArgumentsIn: [statusid, statusData, userid!])
                print(result)
                if result == false {
                    //  出现问题，数据回滚
                    rollBack?.pointee = true
                    break
                }
                
                
            }
            
        }
        
        
    }
    
    //  清空缓存数据
    class func clearCacheData() {
    
        
        let dt = dateFormatter
        dt.dateFormat = "yyyy-MM-dd HH:mm:ss"
        let dateStr = dt.string(from: Date().addingTimeInterval(maxTime))
        
        //  准备sql
        let sql = "delete from statuses where time < '\(dateStr)'"
        
        SqliteManager.sharedManager.queue.inDatabase { (db) in
            let result = db!.executeUpdate(sql, withArgumentsIn: nil)
            if result {
                print("删除成功，影响了\(db!.changes())条数据")
            } else {
                print("删除失败")
            }
        }
        
        
        
    }
    
    
    
    
    
}
